package com.fpt.edu.vn.h2tv.dao;

import java.security.KeyStore.Builder;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;

import javax.persistence.EntityManager;
import javax.persistence.Query;

import org.eclipse.persistence.config.HintValues;
import org.eclipse.persistence.config.QueryHints;

import com.fpt.edu.vn.h2tv.dao.entity.TbBook;

/**
 * A data access object (DAO) providing persistence and search support for
 * TbBook entities. Transaction control of the save(), update() and delete()
 * operations must be handled externally by senders of these methods or must be
 * manually added to each of these methods for data to be persisted to the JPA
 * datastore.
 * 
 * @see com.fpt.edu.vn.h2tv.dao.entity.TbBook
 * @author MyEclipse Persistence Tools
 */

public class TbBookDAO implements ITbBookDAO {
	// property constants
	public static final String BOOKNAME = "bookname";
	public static final String AUTHOR = "author";
	public static final String DESCRIPTION = "description";
	public static final String PRICE = "price";
	public static final String DISCOUNT = "discount";
	public static final String STATUS = "status";
	public static final String CATEGORYID = "categoryid";
	public static final String QUANTITY = "quantity";
	public static final String COUNT = "count";

	private EntityManager getEntityManager() {
		return EntityManagerHelper.getEntityManager();
	}

	/**
	 * Perform an initial save of a previously unsaved TbBook entity. All
	 * subsequent persist actions of this entity should use the #update()
	 * method. This operation must be performed within the a database
	 * transaction context for the entity's data to be permanently saved to the
	 * persistence store, i.e., database. This method uses the
	 * {@link javax.persistence.EntityManager#persist(Object)
	 * EntityManager#persist} operation.
	 * 
	 * <pre>
	 * EntityManagerHelper.beginTransaction();
	 * TbBookDAO.save(entity);
	 * EntityManagerHelper.commit();
	 * </pre>
	 * 
	 * @param entity
	 *            TbBook entity to persist
	 * @throws RuntimeException
	 *             when the operation fails
	 */
	public void save(TbBook entity) {
		EntityManagerHelper.log("saving TbBook instance", Level.INFO, null);
		try {
			getEntityManager().persist(entity);
			EntityManagerHelper.log("save successful", Level.INFO, null);
		} catch (RuntimeException re) {
			EntityManagerHelper.log("save failed", Level.SEVERE, re);
			throw re;
		}
	}

	/**
	 * Delete a persistent TbBook entity. This operation must be performed
	 * within the a database transaction context for the entity's data to be
	 * permanently deleted from the persistence store, i.e., database. This
	 * method uses the {@link javax.persistence.EntityManager#remove(Object)
	 * EntityManager#delete} operation.
	 * 
	 * <pre>
	 * EntityManagerHelper.beginTransaction();
	 * TbBookDAO.delete(entity);
	 * EntityManagerHelper.commit();
	 * entity = null;
	 * </pre>
	 * 
	 * @param entity
	 *            TbBook entity to delete
	 * @throws RuntimeException
	 *             when the operation fails
	 */
	public void delete(TbBook entity) {
		EntityManagerHelper.log("deleting TbBook instance", Level.INFO, null);
		try {
			entity = getEntityManager().getReference(TbBook.class,
					entity.getBooktid());
			getEntityManager().remove(entity);
			EntityManagerHelper.log("delete successful", Level.INFO, null);
		} catch (RuntimeException re) {
			EntityManagerHelper.log("delete failed", Level.SEVERE, re);
			throw re;
		}
	}

	/**
	 * Persist a previously saved TbBook entity and return it or a copy of it to
	 * the sender. A copy of the TbBook entity parameter is returned when the
	 * JPA persistence mechanism has not previously been tracking the updated
	 * entity. This operation must be performed within the a database
	 * transaction context for the entity's data to be permanently saved to the
	 * persistence store, i.e., database. This method uses the
	 * {@link javax.persistence.EntityManager#merge(Object) EntityManager#merge}
	 * operation.
	 * 
	 * <pre>
	 * EntityManagerHelper.beginTransaction();
	 * entity = TbBookDAO.update(entity);
	 * EntityManagerHelper.commit();
	 * </pre>
	 * 
	 * @param entity
	 *            TbBook entity to update
	 * @return TbBook the persisted TbBook entity instance, may not be the same
	 * @throws RuntimeException
	 *             if the operation fails
	 */
	public TbBook update(TbBook entity) {
		EntityManagerHelper.log("updating TbBook instance", Level.INFO, null);
		try {
			TbBook result = getEntityManager().merge(entity);
			EntityManagerHelper.log("update successful", Level.INFO, null);
			return result;
		} catch (RuntimeException re) {
			EntityManagerHelper.log("update failed", Level.SEVERE, re);
			throw re;
		}
	}

	public TbBook findById(Integer id) {
		EntityManagerHelper.log("finding TbBook instance with id: " + id,
				Level.INFO, null);
		try {
			final String queryString = "select model from TbBook model where model.booktid = :propertyValue";
			Query query = getEntityManager().createQuery(queryString);
			query.setParameter("propertyValue", id);
			query.setHint(QueryHints.REFRESH, HintValues.TRUE);
			return (TbBook) query.getSingleResult();
		} catch (RuntimeException re) {
			EntityManagerHelper.log("find failed", Level.SEVERE, re);
			return null;
		}
	}

	/**
	 * Find all TbBook entities with a specific property value.
	 * 
	 * @param propertyName
	 *            the name of the TbBook property to query
	 * @param value
	 *            the property value to match
	 * @param rowStartIdxAndCount
	 *            Optional int varargs. rowStartIdxAndCount[0] specifies the the
	 *            row index in the query result-set to begin collecting the
	 *            results. rowStartIdxAndCount[1] specifies the the maximum
	 *            number of results to return.
	 * @return List<TbBook> found by query
	 */
	@SuppressWarnings("unchecked")
	public List<TbBook> findByProperty(String propertyName, final Object value,
			final int... rowStartIdxAndCount) {
		EntityManagerHelper.log("finding TbBook instance with property: "
				+ propertyName + ", value: " + value, Level.INFO, null);
		try {
			final String queryString = "select model from TbBook model where model."
					+ propertyName + "= :propertyValue";
			Query query = getEntityManager().createQuery(queryString);
			query.setParameter("propertyValue", value);
			if (rowStartIdxAndCount != null && rowStartIdxAndCount.length > 0) {
				int rowStartIdx = Math.max(0, rowStartIdxAndCount[0]);
				if (rowStartIdx > 0) {
					query.setFirstResult(rowStartIdx);
				}

				if (rowStartIdxAndCount.length > 1) {
					int rowCount = Math.max(0, rowStartIdxAndCount[1]);
					if (rowCount > 0) {
						query.setMaxResults(rowCount);
					}
				}
			}
			query.setHint(QueryHints.REFRESH, HintValues.TRUE);
			return query.getResultList();
		} catch (RuntimeException re) {
			EntityManagerHelper.log("find by property name failed",
					Level.SEVERE, re);
			throw re;
		}
	}

	public List<TbBook> findByBookname(Object bookname,
			int... rowStartIdxAndCount) {
		return findByProperty(BOOKNAME, bookname, rowStartIdxAndCount);
	}

	public List<TbBook> findByAuthor(Object author, int... rowStartIdxAndCount) {
		return findByProperty(AUTHOR, author, rowStartIdxAndCount);
	}

	public List<TbBook> findByDescription(Object description,
			int... rowStartIdxAndCount) {
		return findByProperty(DESCRIPTION, description, rowStartIdxAndCount);
	}

	public List<TbBook> findByPrice(Object price, int... rowStartIdxAndCount) {
		return findByProperty(PRICE, price, rowStartIdxAndCount);
	}

	public List<TbBook> findByDiscount(Object discount,
			int... rowStartIdxAndCount) {
		return findByProperty(DISCOUNT, discount, rowStartIdxAndCount);
	}

	public List<TbBook> findByStatus(Object status, int... rowStartIdxAndCount) {
		return findByProperty(STATUS, status, rowStartIdxAndCount);
	}

	public List<TbBook> findByCategoryid(Object categoryid,
			int... rowStartIdxAndCount) {
		return findByProperty(CATEGORYID, categoryid, rowStartIdxAndCount);
	}

	public List<TbBook> findByQuantity(Object quantity,
			int... rowStartIdxAndCount) {
		return findByProperty(QUANTITY, quantity, rowStartIdxAndCount);
	}

	public List<TbBook> findByCount(Object count, int... rowStartIdxAndCount) {
		return findByProperty(COUNT, count, rowStartIdxAndCount);
	}

	/**
	 * Find all TbBook entities.
	 * 
	 * @param rowStartIdxAndCount
	 *            Optional int varargs. rowStartIdxAndCount[0] specifies the the
	 *            row index in the query result-set to begin collecting the
	 *            results. rowStartIdxAndCount[1] specifies the the maximum
	 *            count of results to return.
	 * @return List<TbBook> all TbBook entities
	 */
	@SuppressWarnings("unchecked")
	public List<TbBook> findAll(final int... rowStartIdxAndCount) {
		EntityManagerHelper.log("finding all TbBook instances", Level.INFO,
				null);
		try {
			final String queryString = "select model from TbBook model";
			Query query = getEntityManager().createQuery(queryString);
			if (rowStartIdxAndCount != null && rowStartIdxAndCount.length > 0) {
				int rowStartIdx = Math.max(0, rowStartIdxAndCount[0]);
				if (rowStartIdx > 0) {
					query.setFirstResult(rowStartIdx);
				}

				if (rowStartIdxAndCount.length > 1) {
					int rowCount = Math.max(0, rowStartIdxAndCount[1]);
					if (rowCount > 0) {
						query.setMaxResults(rowCount);
					}
				}
			}
			query.setHint(QueryHints.REFRESH, HintValues.TRUE);
			return query.getResultList();
		} catch (RuntimeException re) {
			EntityManagerHelper.log("find all failed", Level.SEVERE, re);
			throw re;
		}
	}

	// public Long countTotal() {
	// try {
	// StringBuilder queryString = new StringBuilder(
	// "SELECT COUNT(*) FROM tb_book");
	// Query query = getEntityManager().createNativeQuery(
	// queryString.toString());
	// return (Long) query.getSingleResult();
	// } catch (Exception e) {
	// // TODO: handle exception
	// e.printStackTrace();
	// }
	// return null;
	// }
	//
	// public Long countTotalBookCategory(int categoryId) {
	// try {
	// StringBuilder queryString = new StringBuilder(
	// "SELECT COUNT(*) FROM tb_book WHERE CATEGORYID=?id");
	// Query query = getEntityManager().createNativeQuery(
	// queryString.toString());
	// query.setParameter("id", categoryId);
	// return (Long) query.getSingleResult();
	// } catch (Exception e) {
	// // TODO: handle exception
	// e.printStackTrace();
	// }
	// return null;
	// }

	@SuppressWarnings("unchecked")
	@Override
	public List<TbBook> findBookByKeyword(String keyword) {
		StringBuilder builder = new StringBuilder(
				"SELECT * FROM tb_book WHERE BOOKNAME LIKE ?keyWordLike ");
		builder.append(" UNION select book.* from TB_TAGS tags ")
				.append(" left join tb_book book on (tags.BOOKID = book.BOOKTID)")
				.append(" WHERE tags.TAG = ?keyWord;");
		Query query = getEntityManager().createNativeQuery(builder.toString(),
				TbBook.class);
		query.setParameter("keyWordLike", "%" + keyword + "%");
		query.setParameter("keyWord", keyword);
		query.setHint(QueryHints.REFRESH, HintValues.TRUE);
		return query.getResultList();
	}

	// test DAO

	@SuppressWarnings("unchecked")
	public List<TbBook> findAllBook(int start, int limit, int categoryId,
			String keyWord) {
		try {
			if (categoryId != 0 && keyWord == null) {
				StringBuilder queryString = new StringBuilder(
						"SELECT * FROM tb_book WHERE CATEGORYID =?id LIMIT ?start,?limit");
				Query query = getEntityManager().createNativeQuery(
						queryString.toString(), TbBook.class);
				query.setParameter("id", categoryId);
				query.setParameter("start", start);
				query.setParameter("limit", limit);
				return query.getResultList();
			} else if (categoryId == 0 && keyWord == null) {
				StringBuilder queryString = new StringBuilder(
						"SELECT * FROM tb_book LIMIT ?start,?limit");
				Query query = getEntityManager().createNativeQuery(
						queryString.toString(), TbBook.class);
				query.setParameter("start", start);
				query.setParameter("limit", limit);
				return query.getResultList();
			} else if (categoryId == 0 && keyWord != null) {
				StringBuilder queryString = new StringBuilder(
						"SELECT * FROM tb_book WHERE BOOKNAME LIKE ?keyWord LIMIT ?start,?limit");
				Query query = getEntityManager().createNativeQuery(
						queryString.toString(), TbBook.class);
				query.setParameter("keyWord", "%" + keyWord + "%");
				query.setParameter("start", start);
				query.setParameter("limit", limit);
				return query.getResultList();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	public Long count(int categoryId, String keyWord){
		StringBuilder queryString = null;
		if(categoryId == 0 & keyWord == null){
			queryString = new StringBuilder("SELECT COUNT(*) FROM TB_BOOK");
		}else if (categoryId !=0 && keyWord == null){
			queryString = new StringBuilder("SELECT COUNT(*) FROM TB_BOOK WHERE CATEGORYID = ?id");
		}else if (categoryId == 0 && keyWord !=null){
			queryString = new StringBuilder("SELECT COUNT(*) FROM TB_BOOK WHERE BOOKNAME LIKE ?keyWord");
		}
		Query query = getEntityManager().createNativeQuery(queryString.toString());
		query.setParameter("id", categoryId);
		query.setParameter("keyWork", keyWord);
		return (Long) query.getSingleResult();
	}
	
	public static void main(String[] args) {
		TbBookDAO dao = new TbBookDAO();
		Long Book = dao.count(10,null);
		System.out.println(Book);

	}

	@SuppressWarnings("unchecked")
	public List<TbBook> sortBookByCount() {
		String queryString = "SELECT * FROM tb_book order by count desc";
		Query query = getEntityManager().createNativeQuery(queryString,
				TbBook.class);
		query.setHint(QueryHints.REFRESH, HintValues.TRUE);
		return query.getResultList();
	}

	@SuppressWarnings("unchecked")
	public List<TbBook> searchOutOfBook() {
		String queryString = "SELECT * FROM tb_book where quantity=0";
		Query query = getEntityManager().createNativeQuery(queryString,
				TbBook.class);
		query.setHint(QueryHints.REFRESH, HintValues.TRUE);
		return query.getResultList();
	}

	@SuppressWarnings("unchecked")
	public List<TbBook> findHotBook() {
		try {
			StringBuilder queryString = new StringBuilder(
					"SELECT * FROM tb_book ORDER BY COUNT DESC LIMIT 5");
			Query query = getEntityManager().createNativeQuery(
					queryString.toString(), TbBook.class);
			query.setHint(QueryHints.REFRESH, HintValues.TRUE);
			return query.getResultList();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return null;
	}
}